﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace VoteMS.DAL
{
    /*
	 * @author：name
	 * @time：2017/12/26 14:20:56
	 * 
	 */

    /// <summary>
    /// 静态数据访问操作帮助类
    /// 静态类在程序运行时，在服务器内初始化一次，供所有用户共同使用
    /// </summary>
    public static class DBHelper
    {
        public static string connStr =
            System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        #region 操作简易SQL

        public static object ExecScalar(string strSql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(strSql, conn))
                    {
                        object o = cmd.ExecuteScalar();
                        if (object.Equals(o, null) || object.Equals(o, DBNull.Value))
                        {
                            return null;
                        }
                        return o;
                    }
                }
                catch (Exception)
                {
                    return null;
                }
                finally
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 执行简单的SQL查询，得到DataSet
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int ExecNoQuery(string strSql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(strSql, conn))
                    {
                        int number = cmd.ExecuteNonQuery();
                        return number;
                    }
                }
                catch (Exception)
                {
                    return -1;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public static SqlDataReader ExecReader(string strSql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSql, conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception)
            {
                conn.Close();
                return null;
            }
        }

        /// <summary>
        /// 执行简易的SQL语句查询，得到DataSet数据集
        /// </summary>
        /// <param name="strSql">查询SQL语句</param>
        /// <returns></returns>
        public static DataSet Query(string strSql)
        {
            DataSet ds = null;
            using (SqlDataAdapter adapter = new SqlDataAdapter(strSql, connStr))
            {
                ds = new DataSet();
                adapter.Fill(ds);
            }
            return ds;
        }

        #endregion

        #region 操作带有参数的SQL语句

        public static int ExecNoQuery(string strSql, params DbParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(strSql, conn))
                    {
                        /*for(int i = 0; i < parameters.Length; i++)
                        {
                            cmd.Parameters.Add(parameters[i]);
                        }*/
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        int number = cmd.ExecuteNonQuery();
                        return number;
                    }
                }
                catch (Exception ex)
                {
                    return -1;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public static object ExecScalar(string strSql, params DbParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(strSql, conn))
                    {
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        object o = cmd.ExecuteScalar();
                        if (object.Equals(o, null) || object.Equals(o, DBNull.Value))
                        {
                            return null;
                        }
                        return o;
                    }
                }
                catch (Exception)
                {
                    return null;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public static SqlDataReader ExecReader(string strSql, params DbParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSql, conn);
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                conn.Close();

            }
        }

        public static DataSet Query(string strSql, params DbParameter[] parameters)
        {
            DataSet ds = null;
            using (SqlConnection conn = new SqlConnection(strSql))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(strSql, conn);
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    ds = new DataSet();
                    adapter.Fill(ds);
                }
                finally
                {
                    conn.Close();
                }
                return ds;
            }

        }

        #endregion
       
    }
}
